In the bond program, we dealt with the effects of changing interest rates on the volatility of bond prices. This provided a measure of riskiness. The other side of the coin is the return bonds offer. While the various components of this return are easy to determine, computing the actual return requires a little patience.
Finding Information to Determine Returns
Before we discuss the calculation of bond yields, we should point out where to find the needed information. Bond quotes are reported in the financial press only for U.S. government bonds, exchange-listed bonds and a small sample of newly issued bonds. While these quotes are useful and may provide sufficient information to get started on the valuation process, much is omitted. For example, the Wall Street Journal calculates only the current yield for exchange-listed bonds. Yields to maturity are presented only for Treasury bonds and the selected new issues. These listings represent a small part of the traded fixed-income securities. An investor must look elsewhere to obtain information on other fixed income securities.
Moody's Investors Services publishes the monthly Moody's Bond Record and Standard & Poor's publishes the monthly S&P Bond Guide. Both of these compact publications provide current yields and yields to maturity for the bonds they cover. The Bond Guide, for example, lists nearly 7,000 separate issues. These publications also include the information necessary to accurately determine bond yields at times other than the time of publication. These investor information services also offer comprehensive company profiles in other publications, which contain basic information about issued bonds. Finally, investors should be able to obtain the same information from their brokers.
While these information services satisfy many investors' needs, they do not provide the timeliness essential for an active investor or an investor with a large portfolio. The bond guides appear only monthly; many brokers do not have the necessary information available, or may not understand its implications. Moreover, from a planning perspective, an investor wants to
determine a reasonable required return and evaluate whether a prospective bond investment offers that return. Only by being able to determine yields will an investor prepare an appropriate evaluation of bond alternatives.
A Measure of Current Bond Yield
There are several measures of return that are frequently used in bond analysis. These range from the simple to the sublime (or at least the hard to understand). At the simplest end is what we call current yield. The current yield on a bond is just the annual interest payment divided by the current market price of the security.
Annual Interest
Current Yield = -----------------
Market Price
For example, suppose you were interested in IBM's 10.25% bonds that mature in 1995. They are listed bonds, traded on the New York Exchange, and recently closed at 105. (Bond prices are quoted as a percentage of face value, so a quote of 105 means 105% of $1,000, or a price of $1,050.) The current yield is simply:
$102.50
Current Yield = -----------
$1,050.00
= 0.0976 or 9.76%
While current yield is easy to calculate, and is reported in the financial press for listed bonds, it is not a good measure of return for long term fixed income investments. In particular, the current yield does not consider the effect of price changes or the possibility of repayment of principal at maturity.
Yield to Maturity
The most frequently used measure of bond returns over time is the yield to maturity (YTM). As the name implies, this yield measure includes the value of all future cash flows until the bond matures. The yield to maturity is simply the interest rate (or rate of return) that equates the current market price of the bond to the value of all future cash flows until maturity. Determining the cash flows is easy, since they are contractually
fixed. Solving the equation to determine the yield is not so easy. We will use the @IRR function in 1-2-3, but it is important to note how this works.
Lotus 1-2-3's @IRR Function
The term internal rate of return is simply a way of saying the return that makes the value of future cash flows equal to some current amount. 1-2-3's @IRR function operates just the way we would if we were using a calculator to solve the present value equation. That is, we would guess a rate of return and use it to determine the present value of the cash flows. If the guessed return gives a present value above the current market price, it means that our guess was too low. We must use a higher rate of return (or discount rate) to get a lower value. This goes back to our discussion (in the bond program) of the effects of changes in interest rates on bond values. When interest rates go up, bond values fall. Conversely, if our guess results in a value that is below the market price, we must use a lower estimate of the yield. In other words, lower interest rates result in higher bond values. This iterative process continues until we can make the value of the future cash flows equal to the bond's current market price.
The form of the @IRR function is:
@IRR(GUESS,RANGE)
Where: GUESS is an initial estimate of the yield to maturity,
and
RANGE is a range of cells that contain the cash flows
being evaluated.
Implicit in this function is the assumption that the cash flows occur at regularly spaced time periods. If we buy a bond at an interest payment date, this is valid. If not, we must do the trial and error process ourselves.
We could simply use the current yield for our initial guess of the rate, but this might be substantially in error. In 1-2-3 the @IRR function makes 20 attempts to solve the equation. If the program does not get an answer that is correct to within seven decimal places in 20 tries, it gives an error (ERR) message. If this occurs, you make another guess about the initial rate and try again. Usually the error condition is not a problem, but it may arise if the initial guess is far off the mark. We will develop shortly a way of getting a better estimate of the yield to maturity.
Financial Considerations in Using Yield to Maturity
An investment's yield depends on the frequency of cash flows. In the case of most bonds, especially corporate and most Treasury bonds, these cash flows occur every six months. Thus the rate that emerges as the solution is a semiannual return. The usual practice is to simply double that rate, and quote that as the yield to maturity. If we arrived at a return of 5%, we would simply double that and say the yield to maturity was 10%. A more precise way of stating the return would be 10% compounded semiannually, or to convert that rate into an effective annually compounded return of10.25%. Nevertheless, 10% is the way yields to maturity are quoted.
Another frequently ignored factor is the implicit assumption that all cash flows (the periodic interest payments) are reinvested when received at the calculated yield. The mathematics of present value require this. For example, if you decided to take the interest payments as income then your realized return over the time to maturity will be less than the yield to maturity you determined.
In addition, we are presuming that interest rates do not change over the period to maturity. If interest rates do change, our realized rate of return will be different than our estimated yield to maturity. If interest rates increase and you continue to reinvest the interest payments you have received, your realized return will be greater than the yield to maturity. You must remember that the yield is an estimate of future returns -- if you hold the bond to maturity and your market opportunities do not change. Changing market conditions and interest rates will change your returns over time. For this reason, some people refer to yield to maturity as a promised yield or expected yield.
Approximating the Yield to Maturity
Estimating the yield to maturity involves approximating the average annual cash flows and dividing that by an average annual investment over the period to maturity. Determining the average annual cash flows is straightforward. We simply calculate the annual amount of interest by multiplying the annual coupon rate by the face amount.
To estimate the average change in value of the bond until maturity we subtract the current market value from the face amount and divide by the number of years to maturity. This gives the average annual increase in value if the bond is currently selling at a discount from face value. If the bond is selling at a premium, we get the average annual decrease in value.
It is in the estimation of the price changes that the greatest degree of approximation occurs. We are assuming that the price changes by the same amount each year. Even if interest rates remain unchanged, the value of the bond will increase faster, the closer you get to maturity. This approximation gives greater weight to earlier price changes than is actually the case. When we calculate the average value of the bond, we will adjust for this.
The denominator of this equation is simply our average investment in the bond, or its average value between the time we purchase it and maturity. Our first inclination would be to simply add the current price and face amount together and divide by two. But, since the price change is weighted more heavily toward earlier changes, we will assign a weight greater than 50% to the current value. On the basis of a number of simulations, analysts have discovered that a 60% weight for the current value and a 40% weight for the face amount is the best approximation.
cF + (F - P)/T
AYTM = -----------------
(0.6)P + (0.4)F
Where: c is the annual coupon rate,
F is the face amount of the bond,
P is the current market price of the bond, and
T is the number of years to maturity.
Applying this formula to the IBM bond mentioned earlier gives an approximate yield to maturity of:
(0.1025)($1,000) + ($1,000 - $1,050)/7
AYTM = ----------------------------------------
(0.60)($1,050) + (0.40)($1,000)
$102.50 - $7.14
= -------------------
$630.00 + $400.00
$95.36
= ----------- = 0.09258 or 9.258%
$1,030.00
For those of you who are waiting with anticipation, the actual yield to maturity is 9.264%. The approximation gives us a very reasonable guess to use as an input into the @IRR function. The only remaining task is to set up a range with the cash flows to be evaluated.
You may wonder why we should continue if the approximation we derived is so close to the actual yield. There are several reasons. First, the approximation will vary as the current bond price varies. The greater the difference between the current price and the face amount, the greater the error. For example, a deep discount bond selling at 52 ($520.00), paying an 11.25% coupon and maturing in 20 years has an actual yield to maturity
of 21.95%, yet the approximate yield is only 19.17%. This is simply not an acceptable figure with which to plan investment decisions.
Second, by setting up a table to determine the actual yield to maturity, we establish numbers for examining changes in our base assumptions about current interest rates and possible changes. Since the yield to maturity is based on an assumption that market interest rates do not change, an investor will be very interested in determining the effects of potential changes on the realized rate of return.
The rest of this article deals with how the Bondirr spreadsheet was setup to determine the yield to maturity. The necessary formulas appear at the end of the article.
Setting Up the Data Inputs
Figure 1 shows how the inputs are set up. By entering exact dates, we can evaluate yield in between the regular semiannual interest payment dates. 1-2-3's date functions take care of tracking the number of days until the next interest payment. You can set any format you wish; we use dates that are formatted in Lotus' first date format, with day, month and year.
Figure 1
Input Data Items
A B C D E F
1 Data Input Area
2 Month Day Year
3 Current Date.......... 10 11 1988
4 Next Interest Date.... 10 15 1988
6 Maturity Date......... 10 15 1995
7
8 Coupon Rate........... 10.25%
9 Market Price.......... $1,050.00
10 Face Value............ $1,000.00
11
12
13 Accrued Interest: $50.12
14 Approximate YTM: 9.258%
15
16
17 Test YTM............. 9.264%
18 Value of Cash Flows: $0.00
19 Internal Rate of Return: 8.422%
Because the bond may be purchased between interest payment dates, we must account for accrued interest. Since the purchaser of the bond will receive the full semiannual interest payment on the next payment date, he or she must pay to the seller the interest that has accrued since the last payment. We determine this amount by multiplying the semiannual coupon by the proportion of time that passed since the last payment date. This is calculated by the formula in cell D13:
The table for determining the cash flows and their present values is placed one screen to the right of the data input area. The date function takes the date information from the input area and sets up the column of dates. The first two entries, in rows 6 and 7, simply use the input data. The remaining rows use a formula that adds six months (1/2 of an average year, which is 365.25 days) to the preceding date. This formula may result in a case or two of dates that are off by a day; the impact on the calculations is negligible.
The formulas are copied down 60 rows to create enough space for a bond with a 30-year maturity. This will suffice for most bonds. If you wish to evaluate bonds with a longer maturity, simply copy the formula down more rows.
Finally, to determine the actual cash flows and their present values, the following formulas are entered into cells J6 through K7, respectively.
J6: -$D$9-$D$13
K6: +$J$6
J7: +$D$8*$D$10/2
K7: +$J7/((1+$D$17/2)^((I7-$I$6)/182.625))
The dollar signs ($) are entered as indicated so that the original data is referenced when the formulas were copied down the number of rows until maturity. Remember that the last payment includes the repayment of principal, so the last formula is given below. Where this formula appears depends on the maturity of the bond. For the IBM bond, this is in row 21.
J20: +$D$8*$D$10/2+$D$10
Determining the Yield to Maturity
To let you see how the process works, enter the number you determined as the approximate yield to maturity from cell D14 into cell D17. If the value of the cash flows shown in cell D18 is positive, enter a larger yield. If the value in cell D18 is negative, use a smaller number. Continue in this fashion until you get a value that is very close to zero. The actual number
for the yield to maturity in D18 is 0.09264. It would be difficult to get much closer to zero than the value shown. The formula in cell D18 is simply sum of the present values of the cash flows.
Be sure that the range of the @SUM function includes your entire range of discounted cash flows. We have used the entire 30 year range, so the formula does not have to be changed every time we change maturity. Empty cells do not affect the formula. You could also use the @IRR function to give you the answer, as we have programmed in cell D19.
We used the approximate yield to maturity for our initial guess of the value. Since the cash flows are received every six months, we follow the usual convention for simply multiplying the rate by two for the annual return. This formula will give us a significantly different rate when the purchase date is shortly before the next interest payment date. If the purchase is on or shortly after an interest date, the formula works fairly well. The reason is simply the assumption that the cash inflows and outflow occur at equally spaced time intervals.
